/**
 * 
 */
package cn.agiledata.bank.account.dao.hibernate;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.sql.Blob;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Hibernate;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.lob.SerializableBlob;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.SessionFactoryUtils;
import org.springframework.orm.hibernate3.SessionHolder;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import org.springframework.transaction.support.TransactionTemplate;

import cn.agiledata.bank.account.dao.IAccountDAO;
import cn.agiledata.bank.account.service.DefaultUserSummary;
import cn.agiledata.bank.common.util.Constant;
import cn.agiledata.bank.common.util.DateUtil;
import cn.agiledata.bank.common.util.StringUtil;
import cn.agiledata.bank.common.util.WeatherUtil;
import cn.agiledata.bank.hibernateBean.Account;
import cn.agiledata.bank.hibernateBean.BlackList;
import cn.agiledata.bank.hibernateBean.CityInfo;
import cn.agiledata.bank.hibernateBean.MoneyRole;
import cn.agiledata.bank.hibernateBean.OperSubacct;
import cn.agiledata.bank.hibernateBean.OperSubacctId;
import cn.agiledata.bank.hibernateBean.Operator;
import cn.agiledata.bank.hibernateBean.QdTransDetail;
import cn.agiledata.bank.hibernateBean.SubAccount;
import cn.agiledata.bank.hibernateBean.Sysbook;
import cn.agiledata.bank.hibernateBean.SysbookId;
import cn.agiledata.bank.hibernateBean.SystemLog;
import com.vnv.infrastructure.logic.IPBAppFactory;
import com.vnv.infrastructure.logic.IPBHibernate3BaseMgr;
import com.vnv.infrastructure.logic.IPBMgrFactory;

/**
 * IAccountDAO的Hibernate+Spring实现方式.
 * 
 * @author 马贤波
 * 
 * Mar 31, 2006
 */
public class HibernateAccountDAO extends HibernateDaoSupport implements
		IAccountDAO, Constant {

	/**
	 * 根据accountId得到Account对象
	 * 
	 * @author 马贤波
	 * @param accountId
	 * @return
	 * @throws DataAccessException
	 */
	public void updateVerify(Account account) throws DataAccessException {
		this.getHibernateTemplate().update(account);
	}

	public void saveOrUpdateTransDetail(QdTransDetail detail) {
		this.getHibernateTemplate().saveOrUpdate(detail);
	}

	public Account findAccountBySignAvaible(String sign)
			throws DataAccessException {
		if (!StringUtil.isStrEmpty(sign)) {
			sign = sign.toLowerCase();
		}
		// 查询账户

		String userQuery =
		// "select new cn.agiledata.bank.account.service.PersonalAccountSummary" +
		// "(a.id, a.defaultSubAccount.id, a.name, a.email, a.mobile,
		// a.loginPassword) " +
		// "from Account a " +
		// "where lower(a.email)=:sign or a.mobile=:sign or a.id in " +
		// "(select distinct sub.account.id from SubAccount sub where
		// sub.accountId=:sign)" ;
		"from Account a "
				+ "where (trim(lower(a.email))=:sign and a.emStatus = 7) or (a.mobile=:sign and a.mobStatus = 7) or a.accountId=:sign";

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				userQuery, "sign", sign);

		if (resultSet == null || resultSet.size() < 1) {
			return null;
		}

		Account result = (Account) resultSet.get(0);

		return result;
	}

	public Account findAccountBySign(String sign) throws DataAccessException {
		if (!StringUtil.isStrEmpty(sign)) {
			sign = sign.toLowerCase();
		}
		// 查询账户

		String userQuery =
		// "select new cn.agiledata.bank.account.service.PersonalAccountSummary" +
		// "(a.id, a.defaultSubAccount.id, a.name, a.email, a.mobile,
		// a.loginPassword) " +
		// "from Account a " +
		// "where lower(a.email)=:sign or a.mobile=:sign or a.id in " +
		// "(select distinct sub.account.id from SubAccount sub where
		// sub.accountId=:sign)" ;
		"from Account a "
				+ "where trim(lower(a.email))=:sign   or a.mobile=:sign  or a.accountId=:sign";

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				userQuery, "sign", sign);

		if (resultSet == null || resultSet.size() < 1) {
			return null;
		}

		Account result = (Account) resultSet.get(0);

		return result;
	}

	/**
	 * 根据accountId得到Account对象
	 * 
	 * @param accountId
	 * @return
	 * @throws DataAccessException
	 */
	public Account findAccountByAccountId(String accountId)
			throws DataAccessException {
		// 查询账户
		String userQuery = "from Account a "
				+ "where a.accountId = :accountId ";

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				userQuery, "accountId", accountId);

		if (resultSet == null || resultSet.size() < 1) {
			return null;
		}

		Account result = (Account) resultSet.get(0);

		return result;
	}

	public void updateAccount(Account account) throws DataAccessException {
		// account = (Account)this.getHibernateTemplate().merge(account);
		this.getHibernateTemplate().update(account);
	}

	public List findDefaultGroup() {
		return this.getHibernateTemplate().find(
				"from Groups g where g.defaultType='1'");
	}

	public Operator findOperatorByAccount(Account account, String operatorId)
			throws DataAccessException {

		String userQuery = "from Operator o "
				+ "where o.account=:account and o.operatorId=:operatorId and o.valid='1'";

		String[] params = { "account", "operatorId" };
		Object[] values = { account, operatorId };

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				userQuery, params, values);

		if (resultSet == null || resultSet.size() < 1) {
			return null;
		}

		Operator result = (Operator) resultSet.get(0);

		return result;
	}

	public void setDefaultSubAccount(Account account, Long subAccountId)
			throws DataAccessException {
		// this.getHibernateTemplate().clear();
		// this.getHibernateTemplate().flush();
		SubAccount sub = (SubAccount) this.getHibernateTemplate().get(
				SubAccount.class, subAccountId);
		account.setDefaultSubAccount(sub);
		sub.setAccount(account);
		this.getHibernateTemplate().saveOrUpdate(account);

	}

	public List findDetailByOrderId(String orderId) {

		/*String userQuery = "select * from transaction t "
				+ "where t.trans_id = :transId union all select * from QD_TRANSDETAIL qdd where qdd.trans_id=:transId";
		*/
		String userQuery="select t.order_id ,t.trans_id ,t.id,t.amount,t.action_seq,t.trans_status,to_char(t.create_time,'yyyy-mm-dd hh24:mi:ss') as a,t.return_amount,t.remark ,t.credit_seq from transaction t where t.trans_id=:transId union all select qd.order_id ,qd.trans_id ,qd.id,qd.amount,qd.action_seq,qd.trans_status, to_char(qd.create_time,'yyyy-mm-dd hh24:mi:ss') as a,qd.return_amount,qd.remark,qd.credit_seq from qd_transdetail qd where qd.trans_id=:transId";
		String[] params = { "transId" };
		Query queryObject = this.getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createSQLQuery(userQuery);
		queryObject.setParameter("transId", orderId);
		List list = queryObject.list();
		if (list.isEmpty()) {
			return null;
		}
		return list;
	}

	public List getSubAccountDigisters(Account account)
			throws DataAccessException {

		String queryString = "select new cn.agiledata.bank.account.service.SubAccountDigister"
				+ "(sa.id, sa.accountName) "
				+ "from SubAccount sa "
				+ "where sa.account =:account and sa.validFlag = 'Y'";

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				queryString, "account", account);

		if (resultSet == null) {
			resultSet = new ArrayList();
		}

		return resultSet;
	}

	public void updateOtherAccountDetailFlag(Long accountId, Long id) {
		String queryString = " from SubAccount sa where sa.detailFlag = 'Y' and sa.validFlag = 'Y' and sa.account.id = ? and sa.id <> ?";
		List list = getHibernateTemplate().find(queryString,
				new Object[] { accountId, id });
		List newList = new ArrayList();
		if (list != null && list.size() != 0) {
			Iterator iterator = list.iterator();
			while (iterator.hasNext()) {
				SubAccount sa = (SubAccount) iterator.next();
				sa.setDetailFlag(DETAIL_FLAG_FALSE);
				newList.add(sa);
			}
			getHibernateTemplate().saveOrUpdateAll(newList);
		}

	}

	public List getSubAccountOfAccount(Account account)
			throws DataAccessException {
		String queryString = "from SubAccount sa "
				+ "where sa.account =:account and sa.validFlag = 'Y' and (sa.subAccountType='0' or sa.subAccountType is null) ";

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				queryString, "account", account);

		if (resultSet == null) {
			resultSet = new ArrayList();
		}

		return resultSet;
	}

	public SubAccount findBalance(long accountId, String subAccountId)
			throws DataAccessException {
		String queryString = "from SubAccount sa "
				+ "where sa.account.id =:accountID "
				+ "and sa.id =:subAccountId ";
		String[] params = { "accountID", "subAccountId" };
		Object[] values = { new Long(accountId), new Long(subAccountId) };
		List list = (List) this.getHibernateTemplate().findByNamedParam(
				queryString, params, values);
		return (SubAccount) list.get(0);
	}

	public int findCountOfSubAccount(long accountId, String subAccountId,
			Long excluded) throws DataAccessException {
		String queryString = "select sa.id " + "from SubAccount sa "
				+ "where sa.account.id =:accountId "
				+ "and sa.accountId =:subAccountId and sa.validFlag = 'Y'";

		String[] params = { "accountId", "subAccountId" };
		Object[] values = { new Long(accountId), subAccountId };

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				queryString, params, values);

		if (resultSet == null || resultSet.size() == 0) {
			return 0;
		}

		// 修改时，有可能是自身的数值被再次提交，需要判断以下
		if (resultSet.size() == 1 && excluded != null) {
			Long curId = (Long) resultSet.get(0);
			if (curId != null && curId.longValue() == excluded.longValue()) {
				return 0;
			}
		}

		return resultSet.size();
	}

	public void addOrUpdateSubAccount(SubAccount subAccount)
			throws DataAccessException {
		this.getHibernateTemplate().saveOrUpdate(subAccount);
	}

	public void deleteSubAccount(long defaultSubAccountId, long subAccountId)
			throws DataAccessException {
		SubAccount sub = (SubAccount) this.getHibernateTemplate().get(
				SubAccount.class, new Long(subAccountId));
		if (sub.getDetailFlag().equals("Y")) {
			SubAccount defaultSub = (SubAccount) this.getHibernateTemplate()
					.get(SubAccount.class, new Long(defaultSubAccountId));
			defaultSub.setDetailFlag(DETAIL_FLAG_TRUE);
			this.getHibernateTemplate().update(defaultSub);
		}
		sub.setValidFlag("N");
		sub.setDetailFlag(DETAIL_FLAG_FALSE);
		this.getHibernateTemplate().update(sub);
		// this.getHibernateTemplate().delete(sub.getAccountBalance()) ;
	}

	public List getOperatorsOfAccount(Account account)
			throws DataAccessException {
		String queryString = "from Operator o "
				+ "where o.account =:account and o.valid = '1'";

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				queryString, "account", account);

		if (resultSet == null) {
			resultSet = new ArrayList();
		}

		return resultSet;
	}

	public int findCountOfOperator(long accountId, String operatorId,
			Long excluded) throws DataAccessException {
		String queryString = "select o.id " + "from Operator o "
				+ "where o.account.id =:accountId "
				+ "and o.operatorId =:operatorId and o.valid = '1'";

		String[] params = { "accountId", "operatorId" };
		Object[] values = { new Long(accountId), operatorId };

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				queryString, params, values);

		if (resultSet == null || resultSet.size() == 0) {
			return 0;
		}

		// 修改时，有可能是自身的数值被再次提交，需要判断以下
		if (resultSet.size() == 1 && excluded != null) {
			Long curId = (Long) resultSet.get(0);
			if (curId != null && curId.longValue() == excluded.longValue()) {
				return 0;
			}
		}

		return resultSet.size();
	}

	public Operator getOperatorById(Long operatorId) throws DataAccessException {
		return (Operator) this.getHibernateTemplate().get(Operator.class,
				operatorId);
	}

	public void addOrUpdateOperator(Operator operator)
			throws DataAccessException {
		this.getHibernateTemplate().saveOrUpdate(operator);
	}

	public SubAccount getSubAccountById(Long id) {
		return (SubAccount) this.getHibernateTemplate().get(SubAccount.class,
				id);
	}

	public Sysbook getSysBookByTypeIdAndTypeName(String typeId, String typeName) {
		return (Sysbook) this.getHibernateTemplate().get(Sysbook.class,
				new SysbookId(typeName, typeId));
	}

	public void deleteOperator(Long operatorId) throws DataAccessException {
		Operator opr = (Operator) this.getHibernateTemplate().get(
				Operator.class, operatorId);

		opr.setValid(false);
		this.getHibernateTemplate().update(opr);
	}

	public int findCountByEmail(String email) throws DataAccessException {

		email = email.toLowerCase();

		String queryString = "select count(*) from Account a where lower(a.email) = :email or a.mobile=:email ";

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				queryString, "email", email);

		if (resultSet == null || resultSet.size() < 1) {
			return 0;
		}

		Long count = (Long) resultSet.get(0);

		return count.intValue();
	}

	public void createAccount(Account account) throws DataAccessException {
		account.setId(null);
		this.getHibernateTemplate().save(account);
	}

	public void createAccount(final Account acc,
			final InputStream cardPhotoStream,
			final InputStream cardPhotoBackStream, final InputStream photoStream) {

		TransactionTemplate springTransactionTemplate = new TransactionTemplate(
				(PlatformTransactionManager) IPBAppFactory
						.getObj("transactionManager"));
		springTransactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus status) {
				Session session = getHibernateTemplate().getSessionFactory()
						.getCurrentSession();
				if (cardPhotoStream != null) {// 如果是上传身份证图片，先清掉原身份证图片（不管本次上传的是一代证还是二代证，正面图片和背面图片都清掉）
					acc.setTempCardPhoto(Hibernate.createBlob(new byte[1]));
					acc.setTempCardPhotoBack(Hibernate.createBlob(new byte[1]));
					acc.setCardPhoto(Hibernate.createBlob(new byte[1]));
				}
				session.saveOrUpdate(acc);

				session.flush();
				session.refresh(acc, LockMode.UPGRADE);

				if (cardPhotoStream != null) {
					SerializableBlob cardSb = (SerializableBlob) acc
							.getTempCardPhoto();
					Blob cardBlob = cardSb.getWrappedBlob();
					try {
						readPictrueToBlob(cardBlob, cardPhotoStream);
					} catch (Exception e) {
						status.setRollbackOnly();
					}
					acc.setTempCardPhoto(cardBlob);
				}
				if (cardPhotoBackStream != null) {
					SerializableBlob cardSbBack = (SerializableBlob) acc
							.getTempCardPhotoBack();
					Blob cardBlobBack = cardSbBack.getWrappedBlob();
					try {
						readPictrueToBlob(cardBlobBack, cardPhotoBackStream);
					} catch (Exception e) {
						status.setRollbackOnly();
					}
					acc.setTempCardPhotoBack(cardBlobBack);
				}

				if (photoStream != null) {
					SerializableBlob cardPhoto = (SerializableBlob) acc
							.getCardPhoto();
					Blob cardPhotoBack = cardPhoto.getWrappedBlob();
					try {
						readPictrueToBlob(cardPhotoBack, cardPhotoBackStream);
					} catch (Exception e) {
						status.setRollbackOnly();
					}
					acc.setCardPhoto(cardPhotoBack);
				}
				session.saveOrUpdate(acc);
				return null;
			}
		});
	}

	public void deleteAccount(Account account) throws DataAccessException {

	}

	public void dropAccount(Account account) throws DataAccessException {

	}

	public List findBanksOfSubaccountId(Long id) throws DataAccessException {
		String queryString = "select distinct ab "
				+ "from AccountBank ab "
				+ "where (ab.isValid='Y' or ab.isValid is null) and ab.account.id = "
				+ "(select s.account.id from SubAccount s where s.id = :subAccountId )";

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				queryString, "subAccountId", id);

		if (resultSet == null) {
			resultSet = new ArrayList();
		}

		return resultSet;
	}

	public List findTransHisByPara(long email, String startTime, String endTime)
			throws DataAccessException {
		String queryStr = "select * from (select t.order_id ,t.trans_id ,t.id,t.amount,t.action_seq,t.trans_status,to_char(t.create_time,'yyyy-mm-dd hh24:mi:ss') as a,t.return_amount,t.remark from transaction t where 1=1 ";
		if (StringUtils.isNotEmpty(startTime)) {
			queryStr += "and t.create_Time>=to_date('" + startTime
					+ "','yyyy-mm-dd hh24:mi:ss')";
		}
		if (StringUtils.isNotEmpty(endTime)) {
			queryStr += "and t.create_Time<=to_date('" + endTime
					+ "','yyyy-mm-dd hh24:mi:ss')";
		}
		queryStr += " and t.credit_seq=" + email;
		queryStr += " union all select qd.order_id ,qd.trans_id ,qd.id,qd.amount,qd.action_seq,qd.trans_status,to_char(qd.create_time,'yyyy-mm-dd hh24:mi:ss') as a,qd.return_amount,qd.remark from qd_transdetail qd where 1=1";
		if (StringUtils.isNotEmpty(startTime)) {
			queryStr += " and qd.create_Time>=to_date('" + startTime
					+ "','yyyy-mm-dd hh24:mi:ss')";
		}
		if (StringUtils.isNotEmpty(endTime)) {
			queryStr += " and qd.create_Time<=to_date('" + endTime
					+ "','yyyy-mm-dd hh24:mi:ss')";
		}
		queryStr += " and qd.credit_seq=" + email + ") dd"
				+ " order by dd.a desc";
		System.out.println(queryStr+"***************");
		Query queryObject = this.getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createSQLQuery(queryStr);
		List list = queryObject.list();
		if (list.isEmpty()) {
			return null;
		}
		return list;

	}

	public List getAccountBanksByAccountId(long accountId) {
		String hql = "select distinct ab from AccountBank ab "
				+ "where (ab.isValid='Y' or ab.isValid is null) and ab.account.id = ?";
		return getHibernateTemplate().find(hql, new Long(accountId));
	}

	public void updateLog(SystemLog log) throws DataAccessException {
		this.getHibernateTemplate().saveOrUpdate(log);
		this.getHibernateTemplate().flush();
	}

	public Operator deleteOperAccount(Long operid) {
		Operator oper = new Operator();
		List operList = new ArrayList();

		oper = (Operator) getObject(Operator.class, operid);
		Iterator iter = oper.getOperSubaccts().iterator();
		while (iter.hasNext()) {
			OperSubacct oscopy = (OperSubacct) iter.next();

			Sysbook sys1 = (Sysbook) getObject(Sysbook.class, oscopy.getId()
					.getSysbook().getId());
			SubAccount sub1 = (SubAccount) getObject(SubAccount.class, oscopy
					.getId().getSubAccount().getId());

			OperSubacct oscopy1 = (OperSubacct) getObject(OperSubacct.class,
					new OperSubacctId(oper, sys1, sub1));
			operList.add(oscopy1);
		}

		SessionFactory sf = getHibernateTemplate().getSessionFactory();
		SessionHolder holder = (SessionHolder) TransactionSynchronizationManager
				.getResource(sf);
		Session s = holder.getSession();
		// s.flush();
		TransactionSynchronizationManager.unbindResource(sf);
		SessionFactoryUtils.releaseSession(s, sf);

		s = SessionFactoryUtils.getSession(sf, true);
		TransactionSynchronizationManager
				.bindResource(sf, new SessionHolder(s));

		// getHibernateTemplate().evict(oper);

		deleteOperAccount(operList);
		getHibernateTemplate().flush();
		getHibernateTemplate().clear();

		return (Operator) getObject(Operator.class, operid);
	}

	private Object getObject(Class cla, Serializable obj) {

		return getHibernateTemplate().get(cla, obj);
	}

	private void deleteOperAccount(List operList) {
		getHibernateTemplate().deleteAll(operList);
	}

	/**
	 * 
	 */
	public OperSubacct getOperSubacctById(OperSubacctId id) {
		return (OperSubacct) this.getHibernateTemplate().get(OperSubacct.class,
				id);
	}

	public Date getLastvisit(Long id) {
		List list = new ArrayList();

		StringBuffer sb = new StringBuffer();
		sb
				.append("   from SystemLog sl where sl.account.id =:id order by sl.loginTime desc ");
		// sb.append(" ");
		// sb.append(" ");

		SessionFactory sf = null;
		try {
			IPBHibernate3BaseMgr ipb = (IPBHibernate3BaseMgr) IPBMgrFactory
					.getMgr("ipb");
			sf = ipb.getHibernateTemplate().getSessionFactory();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		Session s = SessionFactoryUtils.getSession(sf, true);
		Query q = s.createQuery(sb.toString());
		q.setFirstResult(1);
		q.setMaxResults(2);
		q.setParameter("id", id);
		list = q.list();
		if (list == null) {
			return null;
		}
		if (list == null)
			return null;
		if (list.size() == 0)
			return null;
		SystemLog log = (SystemLog) list.get(0);

		SessionFactoryUtils.releaseSession(s, sf);
		return log.getLoginTime();
	}

	/**
	 * 根据id得到账户
	 * 
	 * @param id
	 * @return
	 */
	public Account getAccountById(Long id) {
		return (Account) this.getHibernateTemplate().get(Account.class, id);
	}

	// public AccountGrade getAccountGrade(Long id) {
	// return
	// (AccountGrade)this.getHibernateTemplate().get(AccountGrade.class,id);
	// }
	/*
	 * public void printAccount(Long id){ }
	 */
	public boolean updateUsername(DefaultUserSummary user, String username) {
		try {
			Account users = getAccountById(user.getUserId());
			users.setName(username);
			users.setId(user.getUserId());
			this.getHibernateTemplate().update(users);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public List getSubAccountOfAccountV(Account account)
			throws DataAccessException {
		String queryString = "from SubAccount sa "
				+ "where sa.account =:account and (sa.subAccountType='0' or sa.subAccountType is null) ";

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				queryString, "account", account);

		if (resultSet == null) {
			resultSet = new ArrayList();
		}

		return resultSet;
	}

	/** wignfeng add on 2006-12-27 */
	/***************************************************************************
	 * find the account from HQL
	 * 
	 * @param paramValueMap
	 * @param hqlString
	 * @return
	 * @throws DataAccessException
	 *             **********************************************************************
	 */
	public List findAccount(Map paramValueMap, String hqlString)
			throws DataAccessException {

		Set keySet;
		Iterator keyIterator;
		String[] params;
		Object[] values;

		keySet = paramValueMap.keySet();
		keyIterator = keySet.iterator();
		params = new String[keySet.size()];
		values = new Object[keySet.size()];

		for (int i = 0; keyIterator.hasNext(); i++) {
			params[i] = (String) keyIterator.next();
			values[i] = paramValueMap.get(params[i]);
		}

		List resultSet = this.getHibernateTemplate().findByNamedParam(
				hqlString, params, values);

		if (resultSet == null || resultSet.size() < 1) {
			return null;
		}

		return resultSet;
	}

	public CityInfo getCityInfoById(String id) {
		String hql = "from CityInfo c where c.cityId = " + id;
		List ls = this.getHibernateTemplate().find(hql);
		if (ls != null && ls.size() > 0) {
			return (CityInfo) ls.get(0);
		} else {
			return new CityInfo();
		}
	}

	public CityInfo getCityInfoBySeq(Long seq) {
		return (CityInfo) this.getObject(CityInfo.class, seq);
	}

	// public Grading getGrading(Long id) {
	// return (Grading)this.getHibernateTemplate().get(Grading.class,id);
	// }

	/** 访问sysbook表，获取登录密码输错次数上限 */
	public int getMaxLoginPwdErrFromSysBook() {
		String queryString = "select list_id from sysbook where type_id='MAX_LOGIN_PWD_ERR'";
		List list = this.getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createSQLQuery(queryString).list();
		if (list.isEmpty()) {
			return 0;
		} else {
			String listIdStr = list.get(0).toString();
			if (listIdStr == null || listIdStr.equals("")) {
				return 0;
			} else {
				try {
					return Integer.parseInt(listIdStr);
				} catch (NumberFormatException e) {
					return 0;
				}
			}
		}
	}

	/** 访问sysbook表，获取支付密码输错次数上限 */
	public int getMaxPayPwdErrFromSysBook() {
		String queryString = "select list_id from sysbook where type_id='MAX_PAY_PWD_ERR'";
		List list = this.getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createSQLQuery(queryString).list();
		if (list.isEmpty()) {
			return 0;
		} else {
			String listIdStr = list.get(0).toString();
			if (listIdStr == null || listIdStr.equals("")) {
				return 0;
			} else {
				try {
					return Integer.parseInt(listIdStr);
				} catch (NumberFormatException e) {
					return 0;
				}
			}
		}
	}

	/** 锁定单个账户（不能登录） */
	public void lockAccountLoginBySign(String sign) throws DataAccessException {
		String updateString = "update Account a set a.status='"
				+ Constant.ACCOUNT_STATE_LOGIN_LOCK
				+ "' where lower(a.email)=:sign or a.mobile=:sign";
		Query queryObject = this.getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createQuery(updateString);
		queryObject.setParameter("sign", sign);
		queryObject.executeUpdate();
	}

	/** 获取账户当天登录密码输错次数 */
	public int getCurrentLoginPwdErrByAccountId(Long id)
			throws DataAccessException {
		Date date = new Date();
		String dateStr = DateUtil.date2String(date, "yyyy-MM-dd");

		String queryString = "select count(id) as count from system_log where login_err_reason='"
				+ Constant.LOGIN_PWD_ERR
				+ "' and account_seq='"
				+ id
				+ "' "
				+ " and to_char(login_time, 'yyyy-MM-dd hh24:mi:ss') like '"
				+ dateStr + "%'";
		Long count = (Long) this.getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createSQLQuery(queryString).addScalar(
						"count", Hibernate.LONG).uniqueResult();
		return count.intValue();
	}

	/** 获取账户当天支付密码输错次数 */
	public int getCurrentPayPwdErrByAccountId(Long id)
			throws DataAccessException {
		Date date = new Date();
		String dateStr = DateUtil.date2String(date, "yyyy-MM-dd");

		String queryString = "select count(id) as count from system_log where login_err_reason='"
				+ Constant.PAY_PWD_ERR
				+ "' and account_seq='"
				+ id
				+ "' "
				+ " and to_char(login_time, 'yyyy-MM-dd hh24:mi:ss') like '"
				+ dateStr + "%'";
		Long count = (Long) this.getHibernateTemplate().getSessionFactory()
				.getCurrentSession().createSQLQuery(queryString).addScalar(
						"count", Hibernate.LONG).uniqueResult();
		return count.intValue();
	}

	public SystemLog getLastLoginSystemLog(String id) {
		// String hql = "from SystemLog t where t.account.id = '"+id+"' and
		// t.loginErrReason is null order by t.loginTime desc";
		// List list = getHibernateTemplate().find(hql);
		// SystemLog log = null;
		// if(list.size()>0){
		// log = (SystemLog) list.get(0);}
		// else{
		// String hql1 = "from SystemLog t where t.account.id = '"+id+"' order
		// by t.loginTime desc";
		// List list1 = getHibernateTemplate().find(hql1);
		// if(list1 != null) {
		// if(list1.size()!=0) {
		// log = (SystemLog) list1.get(0);
		// }
		// }
		//			
		// }
		//		
		// return log;
		String hql = "from SystemLog t1 where t1.loginTime=(select max(t2.loginTime) from SystemLog t2 where t2.account.id='"
				+ id + "' and t2.loginErrReason is null)";
		List list = getHibernateTemplate().find(hql);
		SystemLog log = null;
		if (list.size() == 1) {
			log = (SystemLog) list.get(0);
		}

		return log;
	}

	public SystemLog getLastErrorLoginSystemLog(String id) {
		String hql = "from SystemLog t1 where t1.loginTime=(select max(t2.loginTime) from SystemLog t2 where t2.account.id='"
				+ id + "' and t2.loginErrReason is not null)";
		List list = getHibernateTemplate().find(hql);
		SystemLog log = null;
		if (list.size() == 1) {
			log = (SystemLog) list.get(0);
		}

		return log;
	}

	public boolean checkBlackList(String email) {
		String hql = "from BlackList bl where bl.email=:email";
		List resultSet = this.getHibernateTemplate().findByNamedParam(hql,
				"email", email);
		if (resultSet != null && resultSet.size() > 0) {
			return true;
		} else {
			return false;
		}

	}

	private void readPictrueToBlob(Blob blob, InputStream in)
			throws SQLException, IOException {
		OutputStream out = blob.setBinaryStream(0);
		byte[] bytes = new byte[1024];
		int len = 0;
		while ((len = in.read(bytes)) > 0) {
			out.write(bytes, 0, len);
		}
		out.close();
		in.close();
	}
    public Object queryQdTrans(String hql,Object[] params)throws Exception{
    	List list=getHibernateTemplate().find(hql,params);
    	if (list.size()>0) {
			return 0;
		}
    	return list.get(0);
    }
    public List<MoneyRole> getRule(String hql ,String[] param)throws Exception{
    	return getHibernateTemplate().find(hql, param);
    }

	public void update(Account account) throws Exception {
		getHibernateTemplate().saveOrUpdate(account);
	}
    
}